<%@Page Language="VB"%>

<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.OleDb" %>

<%@ Register TagPrefix="wrox" TagName="connect" Src="..\global\connect-strings.ascx" %>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html><head>
<title>Updating Data with a Command Object</title>
<style type="text/css">
body, td {font-family:Tahoma,Arial,sans-serif; font-size:10pt}
input {font-family:Tahoma,Arial,sans-serif; font-size:9pt}
.heading {font-family:Tahoma,Arial,sans-serif; font-size:14pt; font-weight:bold}
.subhead {font-family:Tahoma,Arial,sans-serif; font-size:12pt; font-weight:bold; padding-bottom:5px}
.cite {font-family:Tahoma,Arial,sans-serif; font-size:8pt}
</style></head>
<body bgcolor="#ffffff">
<span class="heading">Updating Data with a Command Object</span><hr />
<!--------------------------------------------------------------------------->

<%'-- insert connection string script --%>
<wrox:connect id="ctlConnectStrings" runat="server"/>

<div>Connection string: <b><span id="outConnect" runat="server"></span></b></div>
<div>SQL statement: <b><span id="outSQL" runat="server"></span></b></div>
<div id="outError" runat="server">&nbsp;</div>
<div id="outResult" runat="server"></div>

<script language="vb" runat="server">

Sub Page_Load()

   'get connection string from ..\global\connect-strings.ascx user control
   Dim strConnect = ctlConnectStrings.OLEDBConnectionString
   outConnect.InnerText = strConnect 'and display it

   'specify the SQL statement to update the data
   Dim datNow As DateTime = Now()
   Dim strNow As String = datNow.ToString("dd-M-yy \a\t hh:mm:ss")
   Dim strSQL As String
   strSQL = "UPDATE BookList SET Title = 'New Book Written on " _
          & strNow & "' WHERE ISBN='1861003005'"
   outSQL.InnerText = strSQL   'and display it

   'create a new Connection object using the connection string
   Dim objConnect As New OleDbConnection(strConnect)

   'create a new Command using the connection object and select statement
   Dim objCommand As New OleDbCommand(strSQL, objConnect)

   'declare an Integer variable to hold the number of records affected
   Dim intRowsAffected As Integer

   Try

      'open the connection to the database
      objConnect.Open()

      'execute the SQL statement against the command
      'the ExecuteNonQuery method returns the number of records affected
      intRowsAffected = objCommand.ExecuteNonQuery()

   Catch objError As Exception

      'display error details
      outError.InnerHtml = "<b>* Error while updating original data</b>.<br />" _
          & objError.Message & "<br />" & objError.Source
      Exit Sub  ' and stop execution

   End Try

   'declare a string to display the results
   Dim strResult As String

   'show the number of records affected
   strResult = "Executed SQL statement, <b>" & intRowsAffected.ToString() _
      & "</b> record(s) affected<br />Reading back from the database...<br />"

   'read the data back again - specify a SQL statement to
   'extract just this record for the existing Command object
   objCommand.CommandText = "SELECT * FROM BookList WHERE ISBN='1861003005'"

   Try

      'declare a variable to hold a DataReader object
      Dim objDataReader As OleDbDataReader

      'execute the SQL statement against the command to fill the DataReader
      objDataReader = objCommand.ExecuteReader()

      'iterate through the records in the DataReader getting field values
      'the Read method returns False when there are no more records
      Do While objDataReader.Read()

         strResult += "ISBN=""<b>" & objDataReader("ISBN") & "</b>"" &nbsp; Title=""<b>" _
                   & objDataReader("Title") & """</b>"

      Loop

      'close the DataReader and Connection
      objDataReader.Close()
      objConnect.Close()

   Catch objError As Exception

      'display error details
      outError.InnerHtml = "<b>* Error while accessing updated data</b>.<br />" _
          & objError.Message & "<br />" & objError.Source
      Exit Sub  ' and stop execution

   End Try

   'display the result
   outResult.InnerHtml = strResult

End Sub
</script>


<!--------------------------------------------------------------------------->
<hr /><span class="cite">[<a href="..\global\viewsource.aspx">view source</a>] &nbsp; &nbsp; &nbsp; &nbsp;
&copy;2001 <a class="cite" href="http://www.wrox.com/">Wrox Press</a> -
<a class="cite" href="http://www.wrox.com/Books/Book_Details.asp?isbn=1861004885">Professional ASP.NET</a> (ISBN: 1-861004-88-5)</span>
</body>
</html>
